{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "e43aefb9",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+----+----+----+-------+----+\n",
      "|班级|姓名|年龄|性别|   科目|成绩|\n",
      "+----+----+----+----+-------+----+\n",
      "|  12|宋江|  25|  男|chinese|  50|\n",
      "|  12|宋江|  25|  男|   math|  60|\n",
      "|  12|宋江|  25|  男|english|  70|\n",
      "|  12|吴用|  20|  男|chinese|  50|\n",
      "|  12|吴用|  20|  男|   math|  50|\n",
      "|  12|吴用|  20|  男|english|  50|\n",
      "|  12|杨春|  19|  女|chinese|  70|\n",
      "|  12|杨春|  19|  女|   math|  70|\n",
      "|  12|杨春|  19|  女|english|  70|\n",
      "|  13|李逵|  25|  男|chinese|  60|\n",
      "|  13|李逵|  25|  男|   math|  60|\n",
      "|  13|李逵|  25|  男|english|  70|\n",
      "|  13|林冲|  20|  男|chinese|  50|\n",
      "|  13|林冲|  20|  男|   math|  60|\n",
      "|  13|林冲|  20|  男|english|  50|\n",
      "|  13|王英|  19|  女|chinese|  70|\n",
      "|  13|王英|  19|  女|   math|  80|\n",
      "|  13|王英|  19|  女|english|  70|\n",
      "+----+----+----+----+-------+----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 班级  姓名  年龄  性别  科目 成绩\n",
    "# 12 宋江 25 男 chinese 50\n",
    "# 12 宋江 25 男 math 60\n",
    "# 12 宋江 25 男 english 70\n",
    "# 12 吴用 20 男 chinese 50\n",
    "# 12 吴用 20 男 math 50\n",
    "# 12 吴用 20 男 english 50\n",
    "# 12 杨春 19 女 chinese 70\n",
    "# 12 杨春 19 女 math 70\n",
    "# 12 杨春 19 女 english 70\n",
    "# 13 李逵 25 男 chinese 60\n",
    "# 13 李逵 25 男 math 60\n",
    "# 13 李逵 25 男 english 70\n",
    "# 13 林冲 20 男 chinese 50\n",
    "# 13 林冲 20 男 math 60\n",
    "# 13 林冲 20 男 english 50\n",
    "# 13 王英 19 女 chinese 70\n",
    "# 13 王英 19 女 math 80\n",
    "# 13 王英 19 女 english 70\n",
    "\n",
    "#1. 读取文件的数据test.txt\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.types import *\n",
    "from pyspark.sql.functions import *\n",
    "spark = SparkSession.builder.appName('homework2').getOrCreate()\n",
    "schema = StructType([\n",
    "    StructField('班级', IntegerType(), True),\n",
    "    StructField('姓名', StringType(), True),\n",
    "    StructField('年龄', IntegerType(), True),\n",
    "    StructField('性别', StringType(), True),\n",
    "    StructField('科目', StringType(), True),\n",
    "    StructField('成绩', IntegerType(), True)\n",
    "])\n",
    "df = spark.read.csv('data.csv', header=True, schema=schema)\n",
    "df.show()\n",
    "# df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "5c577df0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#2. 一共有多少个小于20岁的人参加考试？\n",
    "df.filter(df['年龄'] < 20).select('姓名').distinct().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "f42c2639",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 3. 一共有多少个等于20岁的人参加考试？\n",
    "df.filter(df['年龄'] == 20).select('姓名').distinct().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "cbe9b7c5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 4. 一共有多少个大于20岁的人参加考试？\n",
    "df.filter(df['年龄'] > 20).select('姓名').distinct().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "c1688da5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 5. 一共有多个男生参加考试？\n",
    "df.filter(df['性别'] == '男').select('姓名').distinct().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "7cd6f054",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 6. 一共有多少个女生参加考试？\n",
    "df.filter(df['性别'] == '女').select('姓名').distinct().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "5c8dd5bf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 7. 12班有多少人参加考试？\n",
    "df.filter(df['班级'] == '12').select('姓名').distinct().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "6eb33e95",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 8. 13班有多少人参加考试？\n",
    "df.filter(df['班级'] == '13').select('姓名').distinct().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "d4f68f6e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------+\n",
      "|         avg(成绩)|\n",
      "+------------------+\n",
      "|58.333333333333336|\n",
      "+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 9. 语文科目的平均成绩是多少？\n",
    "df.filter(df['科目'] == 'chinese').select(avg(col('成绩'))).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "4aaa8854",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------+\n",
      "|         avg(成绩)|\n",
      "+------------------+\n",
      "|63.333333333333336|\n",
      "+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 10. 数学科目的平均成绩是多少？\n",
    "df.filter(df['科目'] == 'math').select(avg(col('成绩'))).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "fd191278",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------+\n",
      "|         avg(成绩)|\n",
      "+------------------+\n",
      "|63.333333333333336|\n",
      "+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 11. 英语科目的平均成绩是多少？\n",
    "df.filter(df['科目'] == 'english').select(avg(col('成绩'))).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "df75a2a3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+----+------------------+\n",
      "|班级|姓名|         avg(成绩)|\n",
      "+----+----+------------------+\n",
      "|  12|吴用|              50.0|\n",
      "|  12|杨春|              70.0|\n",
      "|  13|李逵|63.333333333333336|\n",
      "|  13|王英| 73.33333333333333|\n",
      "|  12|宋江|              60.0|\n",
      "|  13|林冲|53.333333333333336|\n",
      "+----+----+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 12. 每个人平均成绩是多少？\n",
    "df.groupBy(['班级', '姓名']).agg({'成绩':'avg'}).show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "65796665",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+\n",
      "|avg(成绩)|\n",
      "+---------+\n",
      "|     60.0|\n",
      "+---------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 13. 12班平均成绩是多少？\n",
    "df.filter(df['班级'] == '12').select(avg('成绩')).show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "d409f79b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------+\n",
      "|avg(sum(成绩))|\n",
      "+--------------+\n",
      "|         165.0|\n",
      "+--------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 14. 12班男生平均总成绩是多少？\n",
    "tmp_df = df.filter(df['班级'] == '12').filter(df['性别'] == '男').groupBy(['班级', '姓名']).agg({'成绩':'sum'})\n",
    "tmp_df.select(avg(col('sum(成绩)'))).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "4fb9a177",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------+\n",
      "|avg(sum(成绩))|\n",
      "+--------------+\n",
      "|         210.0|\n",
      "+--------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 15. 12班女生平均总成绩是多少？\n",
    "tmp_df = df.filter(df['班级'] == '12').filter(df['性别'] == '女').groupBy(['班级', '姓名']).agg({'成绩':'sum'})\n",
    "tmp_df.select(avg(col('sum(成绩)'))).show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "b6367ce7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------+\n",
      "|         avg(成绩)|\n",
      "+------------------+\n",
      "|63.333333333333336|\n",
      "+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 16. 13班平均成绩是多少？\n",
    "df.filter(df['班级'] == '13').select(avg('成绩')).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "40bea716",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------+\n",
      "|avg(sum(成绩))|\n",
      "+--------------+\n",
      "|         175.0|\n",
      "+--------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 17. 13班男生平均总成绩是多少？\n",
    "tmp_df = df.filter(df['班级'] == '13').filter(df['性别'] == '男').groupBy(['班级', '姓名']).agg({'成绩':'sum'})\n",
    "tmp_df.select(avg(col('sum(成绩)'))).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "89bbd157",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------+\n",
      "|avg(sum(成绩))|\n",
      "+--------------+\n",
      "|         220.0|\n",
      "+--------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 18. 13班女生平均总成绩是多少？\n",
    "tmp_df = df.filter(df['班级'] == '13').filter(df['性别'] == '女').groupBy(['班级', '姓名']).agg({'成绩':'sum'})\n",
    "tmp_df.select(avg(col('sum(成绩)'))).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "8b59fe6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 19. 全校语文成绩最高分是多少？\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "aecc1d4f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 20. 12班语文成绩最低分是多少？\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "ccf5b006",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 21. 13班数学最高成绩是多少？\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "6a67afe6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 22. 总成绩大于150分的12班的女生有几个？\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bbc4b7bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 23. 总成绩大于150分，且数学大于等于70，且年龄大于等于19岁的学生的平均成绩是多少？"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
